package util;

import hw.Users;

import javax.imageio.plugins.tiff.ExifGPSTagSet;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AtmApi {
    public static Connection getConnection() {
        Connection con = null;
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/hw?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root", "kevind110");
            System.out.println("con:" + con);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return con;
    }

    //更新
    public static void inset(Users users) {
        Connection con = getConnection();
        PreparedStatement statement = null;

        String sql = "UPDATE t_user set u_balance = ? , u_password=?  where u_id = ? " ;
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,users.getBalance());
            statement.setObject(2,users.getPassword());
            statement.setObject(3,users.getId());
            int row = statement.executeUpdate();
            System.out.println(row);
        } catch (SQLException exception) {
            exception.printStackTrace();
        } finally {
            close(con, statement);
        }
    }

//    public static void changpw(Users users1,int ) {
//        Connection con = getConnection();
//        PreparedStatement statement = null;
//        String sql = "UPDATE t_user set u_balance = ? , u_password=?  where u_id = ? " ;
//        try {
//            statement = con.prepareStatement(sql);
//            statement.setObject(1,users1.getBalance());
//            statement.setObject(2,users1.getPassword());
//            statement.setObject(3,users1.getId());
//        } catch (SQLException exception) {
//            exception.printStackTrace();
//        } finally {
//            close(con, statement);
//        }
//    }
//通过姓名查找
public static void inset(Users users,Users users2) {
    Connection con = getConnection();
    PreparedStatement statement = null;
    String sql = "UPDATE t_user set u_balance = ? , u_password=?  where u_id = ? " ;
    try {
        con.setAutoCommit(false);
        statement = con.prepareStatement(sql);
        statement.setObject(1,users.getBalance());
        statement.setObject(2,users.getPassword());
        statement.setObject(3,users.getId());
        int row = statement.executeUpdate();
        System.out.println(row);
        statement = con.prepareStatement(sql);
        statement.setObject(1,users2.getBalance());
        statement.setObject(2,users2.getPassword());
        statement.setObject(3,users2.getId());
            row = statement.executeUpdate();
            con.commit();
        System.out.println(row);
    } catch (SQLException exception) {
        exception.printStackTrace();
        try {
            con.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    } finally {
        close(con, statement);
    }
}


    public Users getByName(String username ) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        Users users = null;
        String sql =  "select * from t_user where u_name =?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,username);
            rs = statement.executeQuery();
            if (rs.next()){
                users = new Users();
                users.setId(rs.getInt("u_id"));
                users.setName(rs.getString("u_name"));
                users.setPassword(rs.getString("u_password"));
                users.setBalance(rs.getInt("u_balance"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        finally {
            close(con,statement, rs);
        }
        return  users;
    }


    //通过姓名和密码查找
    public Users getByNameAndPwd(String username,String pwd ) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        Users users = null;
        String sql =  "select * from t_user where u_name =? and u_password = ?   ";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,username);
            statement.setObject(2,pwd);
            rs = statement.executeQuery();
            if (rs.next()){
                users = new Users();
                users.setId(rs.getInt("u_id"));
                users.setName(rs.getString("u_name"));
                users.setPassword(rs.getString("u_password"));
                users.setBalance(rs.getInt("u_balance"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        finally {
            close(con,statement, rs);
        }
        return  users;
    }

    public static List query() {
        Connection con = getConnection();
        Statement statement = null;
        ResultSet rs = null;
        List userList = new ArrayList();
        try {
            statement = con.createStatement();
            String sql = "select * from t_user";
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Users users = new Users();
                users.setId(rs.getInt("u_id"));
                users.setBalance(rs.getInt("u_balance"));
                users.setName(rs.getString("u_name"));
                users.setPassword(rs.getString("u_password"));
                userList.add(users);
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return userList;
    }


    public static void close(Connection con, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
                con.close();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
        }
    }

    public static void close(Connection con, Statement statement, ResultSet rs) {
        if (statement != null) {
            try {
                rs.close();
                statement.close();
                con.close();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
        }
    }

    static String s;

    public static void main(String[] args) {
        AtmApi atmApi = new AtmApi();
        System.out.println( atmApi.getByNameAndPwd("张三","3"));

    }
}
